/*
 * @(#)InitialServlet.java   
 *
 * Project: CUPSecureBM
 * 
 * Modify Information:
 *  Author          Date                Description
 * ==============   =================   =====================
 *  yonggangguo     2005-12-29             Create this file
 *
 * Copyright notice:
 *   Copyright (c) 2001-2005 Beijing HiTRUST Technology Co., Ltd.
 *   1808 Room, Science & Technology Building, No. 9 South Street,
 *   Zhong Guan Cun, Haidian District, Beijing ,100081, China
 *   All rights reserved.
 *
 *   This software is the confidential and proprietary information of
 *   HiTRUST.COM, Inc. ('Confidential Information').  You shall not
 *   disclose such Confidential Information and shall use it only in
 *   accordance with the terms of the license agreement you entered
 *   into with HiTRUST.
 */

package com.hitrust.gs.action.report;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;
import java.util.Timer;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.PropertyConfigurator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import javax.servlet.http.HttpServletRequest;

import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.business.GSManufactureProcessor;
import com.hitrust.gs.dao.GSOrganizationDAO;
import com.hitrust.gs.dao.GSProviderDAO;
import com.hitrust.gs.integration.GSApepInfoProcess;
import com.hitrust.gs.integration.GSServerListener;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.db.DBConnectionManager;
import com.hitrust.gs.lib.db.backup.DataTask;
import com.hitrust.gs.net.GSAuditFlowManager;
import com.hitrust.gs.service.GSBaseManager;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.lang.StringUtils;
import com.hitrust.gs.util.menu.BuildTreeMenu;
import com.hitrust.gs.util.security.Base64;


public class GSStockQuantityMonthSumReportServlet extends HttpServlet {
	
	public static String FILENAME="STOCKQUANTMONTHSUM";
	
    public void init(ServletConfig aConfig) throws ServletException {
        super.init(aConfig);
    }

    
    /**
     * 订货单位采购明细表,按订货单位和签收时间排序
     * @param start
     * @param end
     * @return
     */
    public ArrayList deptdetailquery(String start,String end,String deptid){
        
        Object[] obj = new Object[2]; 
        String orgLimitCond = "";
        if(deptid.equals("0000")){
            orgLimitCond = "1=1";
        }else{
        	if(deptid.substring(2,4).equals("00"))
        	{
				orgLimitCond = "T2.gs_upperorg_id = ?";
        	}else
        	{
				orgLimitCond = "T2.gs_org_id = ? or T2.gs_upperorg_id = ?";
        	}
            
        }
        //普通
        String sql1 = "select T1.* from " +
            	"(select a.gs_workcorp_id P1,c.gs_org_name P2,d.gs_concentricity_id P3, " +
        		"d.gs_manufacture_id P4,d.gs_manufacture_name P5,d.gs_manufacture_spec P6, " +
        		"d.gs_manufacture_type P7,d.gs_manufacture_brand P8, " +
        		"b.GS_GOODS_UNITPRICE P9,b.gs_order_count P10,d.GS_QUANT_UNIT P11, " +
        		"b.GS_SIGNIN_DATE P12,b.GS_GOODS_UNITPRICE * b.gs_order_count P13,a.gs_order_tempid P14  " +
        		"from gs_order a,gs_order_detail b ,gs_organization c,gs_manufacture d where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and " +
        		"(b.gs_order_detail_status = 12  or b.gs_order_detail_status = 14 ) and "+
        		"a.gs_order_tempid = b.gs_order_tempid and " +
        		"a.gs_workcorp_id = c.gs_org_id and " +
        		"b.gs_manufacture_id = d.gs_manufacture_id " + 
        		"order by P1,P12 ) T1 where exists (" +
        		"select gs_org_id from gs_organization T2 " +
        		"where T1.P1 = T2.gs_org_id and (" + orgLimitCond + "))";
        GSOrganizationDAO dao = new GSOrganizationDAO();
        Connection conn = null;
        ResultSet rs = null;
        ArrayList ary = new ArrayList();
        try{
            conn = dao.getConnetion();
            PreparedStatement pstmt = conn.prepareStatement(sql1);
            
            pstmt.setString(1,start);
            pstmt.setString(2,end);
            if(!deptid.equals("0000")){
            	if(deptid.substring(2,4).equals("00"))
            	{
					pstmt.setString(3,deptid);
            	}else
            	{
                	pstmt.setString(3,deptid);
                	pstmt.setString(4,deptid);
            	}
            }
            rs = pstmt.executeQuery();
            
            if(rs!=null){
                while(rs.next()){
                    
                    String workcorpid = rs.getString("P1") == null ? "" : rs.getString("P1");
                    String orgname = rs.getString("P2") == null ? "" : rs.getString("P2");
                    String concentricityid = rs.getString("P3") == null ? "" :rs.getString("P3");
                    String manufactureid = rs.getString("P4") == null ? "" :rs.getString("P4");
                    String manufacturename = rs.getString("P5") == null ? "" : rs.getString("P5");
                    String manufacturespec = rs.getString("P6") == null ? "" :rs.getString("P6");
                    String manufacturetype = rs.getString("P7") == null ? "" :rs.getString("P7");
                    String manufacturebrand = rs.getString("P8") == null ? "" :rs.getString("P8");
                    String unitprice = rs.getString("P9") == null ? "0" :rs.getString("P9");
                    String ordercount = rs.getString("P10") == null ? "0" :rs.getString("P10");
                    String quantunit = rs.getString("P11") == null ? "" :rs.getString("P11");
                    String signdate = rs.getString("P12") == null ? "" : rs.getString("P12");
                    String totalprice = rs.getString("P13") == null ? "0" :rs.getString("P13");
                    String orderid = rs.getString("P14") == null ? "" :rs.getString("P14");
                    String[] tmp = new String[]{orgname,manufactureid,concentricityid,
                            manufacturename,manufacturespec,manufacturetype,manufacturebrand,
                            unitprice,ordercount+quantunit,signdate,totalprice,orderid};
                    ary.add(tmp);

                }
                rs.close();                
            }
            
        }catch(Exception e){
            GSLogWriter.error(this,e.getMessage());
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        
        return ary;
    }

    /**
     * 订货单位采购汇总表，按订货单位和产品排序
     * @param start
     * @param end
     * @return
     */
    public ArrayList deptsumquery(String start,String end,String deptid){
        
        Object[] obj = new Object[2]; 
        //普通
        String orgLimitCond = "";
        if(deptid.equals("0000")){
            orgLimitCond = "1=1";
        }else{
			if(deptid.substring(2,4).equals("00"))
				{
					orgLimitCond = "T2.gs_upperorg_id = ?";
				}else
				{
					orgLimitCond = "T2.gs_org_id = ? or T2.gs_upperorg_id = ?";
				}
        }
        String sql1 = "select T1.* from " +
		    	"(select a.gs_workcorp_id P1,c.gs_org_name P2,d.gs_concentricity_id P3, " +
				"d.gs_manufacture_id P4,d.gs_manufacture_name P5,d.gs_manufacture_spec P6, " +
				"d.gs_manufacture_type P7,d.gs_manufacture_brand P8, " +
				"b.GS_GOODS_UNITPRICE P9,b.gs_order_count P10,d.GS_QUANT_UNIT P11, " +
				"b.GS_SIGNIN_DATE P12 " +
				"from gs_order a, gs_order_detail b, gs_organization c, gs_manufacture d, gs_provider e where " +
				"b.gs_signin_date >= ? and "+
				"b.gs_signin_date <= ? and "+
				"(b.gs_order_detail_status = 12  or b.gs_order_detail_status = 14 ) and "+
				"a.gs_order_tempid = b.gs_order_tempid and " +
				"a.gs_workcorp_id = c.gs_org_id and " +
				"b.gs_manufacture_id = d.gs_manufacture_id and " + 
				"b.gs_provider_id = e.gs_provider_id " +
				"order by P1,P4 ) T1 where exists (" +
				"select gs_org_id from gs_organization T2 " +
				"where T1.P1 = T2.gs_org_id and ("+ orgLimitCond +"))";

        GSOrganizationDAO dao = new GSOrganizationDAO();
        Connection conn = null;
        ResultSet rs = null;
        ArrayList ary = new ArrayList();
        try{
            conn = dao.getConnetion();
            PreparedStatement pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1,start);
            pstmt.setString(2,end);
			if(!deptid.equals("0000")){
				if(deptid.substring(2,4).equals("00"))
				{
					pstmt.setString(3,deptid);
				}else
				{
					pstmt.setString(3,deptid);
					pstmt.setString(4,deptid);
				}
			}
            rs = pstmt.executeQuery();
            
            String manufactureid_ = "";
            if(rs!=null){
                while(rs.next()){
                    
                    String workcorpid = rs.getString("P1");
                    String orgname = rs.getString("P2");
                    String concentricityid = rs.getString("P3");
                    String manufactureid = rs.getString("P4");
                    String manufacturename = rs.getString("P5");
                    String manufacturespec = rs.getString("P6");
                    String manufacturetype = rs.getString("P7");
                    String manufacturebrand = rs.getString("P8");
                    String unitprice = rs.getString("P9");
                    String ordercount = rs.getString("P10");
                    String quantunit = rs.getString("P11");
                    //String signdate = rs.getString("P12");

                    String[] tmp = null;
                    if(manufactureid_.equals(manufactureid)){
                        tmp = (String[])ary.get(ary.size()-1);
                        tmp[7] = new BigDecimal(tmp[7]).add(new BigDecimal(ordercount)).toString();
                        BigDecimal price = new BigDecimal(unitprice).multiply(new BigDecimal(ordercount));
                        tmp[9] = price.add(new BigDecimal(tmp[9])).toString();
                    }else{
                        String price = new BigDecimal(unitprice).multiply(new BigDecimal(ordercount)).toString();
                        tmp = new String[]{orgname,manufactureid,concentricityid,
                                manufacturename,manufacturespec,manufacturetype,manufacturebrand,
                                ordercount,quantunit,price};    
                        ary.add(tmp);
                        manufactureid_ = manufactureid;
                    }
                }
                rs.close();                
            }
            
        }catch(Exception e){
            GSLogWriter.error(this,e.getMessage());
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        
        return ary;
    }

    /**
     * 订货单位采购明细表,按订货单位和签收时间排序
     * @param start
     * @param end
     * @return
     */
    public ArrayList provdetailquery(String start,String end,String provid,String operOrgId){
        
        Object[] obj = new Object[2]; 
        //普通
        String orgLimitCond = "";
        if(operOrgId.equals("0000")){//如果是巴士物资人员,可见到所有运营公司和下属机构
            orgLimitCond = "c.gs_org_id like '%00' or c.gs_upperorg_id like '%00'";
        }else{//如果是运营公司/修理厂人员,只能看见自己单位和下属机构
            
			if(operOrgId.substring(2,4).equals("00"))
			{
				orgLimitCond = "c.gs_upperorg_id = '"+operOrgId+"'";
			}else
			{
				orgLimitCond = "c.gs_org_id = '"+operOrgId+"' or c.gs_upperorg_id = '"+operOrgId+"'";
			}
        }
        String sql1 = 
            	"select a.gs_workcorp_id P1,c.gs_upperorg_id P1_,c.gs_org_name P2,d.gs_concentricity_id P3, " +
        		"d.gs_manufacture_id P4,d.gs_manufacture_name P5,d.gs_manufacture_spec P6, " +
        		"d.gs_manufacture_type P7,d.gs_manufacture_brand P8, " +
        		"b.GS_GOODS_UNITPRICE P9,b.gs_order_count P10,d.GS_QUANT_UNIT P11, " +
        		"b.GS_SIGNIN_DATE P12,b.GS_GOODS_UNITPRICE * b.gs_order_count P13 " +
        		"from gs_order a,gs_order_detail b ,gs_organization c,gs_manufacture d,gs_provider e where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and "+
        		"(b.gs_order_detail_status = 12  or b.gs_order_detail_status = 14 ) and "+
        		"a.gs_order_tempid = b.gs_order_tempid and " +
        		"a.gs_workcorp_id = c.gs_org_id and " +
        		"("+ orgLimitCond +") and " +
        		"b.gs_manufacture_id = d.gs_manufacture_id and " +
        		"e.gs_provider_shortname = ? and e.gs_provider_id = b.gs_provider_id " + 
        		"order by P1,P12   ";
        GSOrganizationDAO dao = new GSOrganizationDAO();
        Connection conn = null;
        ResultSet rs = null;
        ArrayList ary = new ArrayList();
        HashMap map = new HashMap();
        try{
            conn = dao.getConnetion();
            PreparedStatement pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1,start);
            pstmt.setString(2,end);
            pstmt.setString(3,provid);

            rs = pstmt.executeQuery();
            
            if(rs!=null){
                while(rs.next()){
                    
                    
                    String upperorgid = rs.getString("P1_");
                    String workcorpid = null;
                    if(upperorgid.substring(2).equals("00")){
                        workcorpid = rs.getString("P1");
                    }else{
                        workcorpid = rs.getString("P1.5");
                    }
                    ;
                    String orgname = rs.getString("P2");
                    String concentricityid = rs.getString("P3");
                    String manufactureid = rs.getString("P4");
                    String manufacturename = rs.getString("P5");
                    String manufacturespec = rs.getString("P6");
                    String manufacturetype = rs.getString("P7");
                    String manufacturebrand = rs.getString("P8");
                    String unitprice = rs.getString("P9");
                    String ordercount = rs.getString("P10");
                    String quantunit = rs.getString("P11");
                    String signdate = rs.getString("P12");
                    String totalprice = rs.getString("P13");
                    String[] tmp = new String[]{orgname,manufactureid,concentricityid,
                            manufacturename,manufacturespec,manufacturetype,manufacturebrand,
                            unitprice,ordercount+quantunit,signdate,totalprice,workcorpid};

                    if(map.containsKey(workcorpid)){
                        ArrayList temp = (ArrayList)map.get(workcorpid);
                        temp.add(tmp);
                    }else{
                        ArrayList temp = new ArrayList();
                        temp.add(tmp);
                        map.put(workcorpid,temp);
                    }
                    
                }
                rs.close();                
            }
            
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        Iterator ite = map.keySet().iterator();
        while(ite.hasNext()){
            ary.addAll((ArrayList)map.get(ite.next()));
        }
        Collections.sort(ary,new com.hitrust.gs.util.MyComparator());
        
        return ary;
    }

    
    /**
     * 订货单位采购明细表,按订货单位和签收时间排序
     * @param start
     * @param end
     * @return
     */
    public ArrayList provsumquery(String start,String end,String provid,String operOrgId){
        
        Object[] obj = new Object[2]; 
        //普通
        String orgLimitCond = "";
        if(operOrgId.equals("0000")){//如果是巴士物资人员,可见到所有运营公司和下属机构
            orgLimitCond = "c.gs_org_id like '%00' or c.gs_upperorg_id like '%00'";
        }else{//如果是运营公司/修理厂人员,只能看见自己单位和下属机构
			if(operOrgId.substring(2,4).equals("00"))
			{
				orgLimitCond = "c.gs_upperorg_id = '"+operOrgId+"'";
			}else
			{
				orgLimitCond = "c.gs_org_id = '"+operOrgId+"' or c.gs_upperorg_id = '"+operOrgId+"'";
			}
        }
        String sql1 = 
            	"select a.gs_workcorp_id P1,c.gs_upperorg_id P1_,c.gs_org_name P2,d.gs_concentricity_id P3, " +
        		"d.gs_manufacture_id P4,d.gs_manufacture_name P5,d.gs_manufacture_spec P6, " +
        		"d.gs_manufacture_type P7,d.gs_manufacture_brand P8, " +
        		"b.GS_GOODS_UNITPRICE P9,b.gs_order_count P10,d.GS_QUANT_UNIT P11, " +
        		"b.GS_SIGNIN_DATE P12,b.GS_GOODS_UNITPRICE * b.gs_order_count P13 " +
        		"from gs_order a,gs_order_detail b ,gs_organization c,gs_manufacture d,gs_provider e where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and "+
        		"(b.gs_order_detail_status = 12  or b.gs_order_detail_status = 14 ) and "+
        		"a.gs_order_tempid = b.gs_order_tempid and " +
        		"a.gs_workcorp_id = c.gs_org_id and " +
        		"("+ orgLimitCond +") and " +
        		"b.gs_manufacture_id = d.gs_manufacture_id and " +
        		"e.gs_provider_shortname = ? and e.gs_provider_id = b.gs_provider_id " + 
        		"order by P1   ";
        GSOrganizationDAO dao = new GSOrganizationDAO();
        Connection conn = null;
        ResultSet rs = null;
        ArrayList ary = new ArrayList();
        HashMap map = new HashMap();
        try{
            conn = dao.getConnetion();
            PreparedStatement pstmt = conn.prepareStatement(sql1);
            pstmt.setString(1,start);
            pstmt.setString(2,end);
            pstmt.setString(3,provid);

            rs = pstmt.executeQuery();
            
            if(rs!=null){
                while(rs.next()){
                    
                    
                    String upperorgid = rs.getString("P1_");
                    String workcorpid = null;
                    //把属于同一个运营公司的修理厂,合并到所属的运营公司上
                    if(upperorgid.substring(2).equals("00")){
                        workcorpid = rs.getString("P1");
                    }else{
                        workcorpid = rs.getString("P1.5");//把订货单位为修理厂的替换为所属运营公司
                    }
                    ;
                    String orgname = rs.getString("P2");
                    String concentricityid = rs.getString("P3");
                    String manufactureid = rs.getString("P4");
                    String manufacturename = rs.getString("P5");
                    String manufacturespec = rs.getString("P6");
                    String manufacturetype = rs.getString("P7");
                    String manufacturebrand = rs.getString("P8");
                    String unitprice = rs.getString("P9");
                    String ordercount = rs.getString("P10");
                    String quantunit = rs.getString("P11");
                    //String signdate = rs.getString("P12");
                    String totalprice = rs.getString("P13");
                    String[] tmp = new String[]{orgname,manufactureid,concentricityid,
                            manufacturename,manufacturespec,manufacturetype,manufacturebrand,
                            ordercount,quantunit,unitprice,workcorpid};

                    //如果同一订货单位的同一产品,则合并订货数量
                    //否则新增一笔订货产品行
                    if(map.containsKey(workcorpid)){
                        ArrayList temp = (ArrayList)map.get(workcorpid);
                        boolean flag = true;
                        for(int size=0;size<temp.size();size++){
                            String[] buffer = (String[])temp.get(size);
                            if(buffer[1].equals(tmp[1])){
                                buffer[7] = new BigDecimal(buffer[7]).add(new BigDecimal(tmp[7])).toString();
                                flag = false;
                                break;
                            }
                        }
                        if(flag)
                            temp.add(tmp);
                    }else{
                        ArrayList temp = new ArrayList();
                        temp.add(tmp);
                        map.put(workcorpid,temp);
                    }
                    
                }
                rs.close();                
            }
            
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        //组合成一个矢量
        Iterator ite = map.keySet().iterator();
        while(ite.hasNext()){
            ary.addAll((ArrayList)map.get(ite.next()));
        }
        return ary;
    }

    public void doPost(HttpServletRequest request,HttpServletResponse response)throws ServletException {
    	
        System.out.println("\ninto GSStockQuatityMonthSumReportServlet");
        
	    String start = request.getParameter("begindate");
        String end = request.getParameter("enddate");
        String deptid = request.getParameter("deptno");
    	String depetype = request.getParameter("depttype");
    	String title = request.getParameter("filetitle");
    	String querytype = request.getParameter("querytype");
    	
        if("1".equals(querytype)){//如果采用的是指定月份查询
            String[] template = GSBaseManager.getPreSetDate(Integer.parseInt(start),Integer.parseInt(end));
            start = template[0].trim() + " 00:00:00";
            end = template[1].trim() + " 23:59:59";
        }else{//如果采用的是指定首未时间查询
            start = start + " 00:00:00";
            end = end + " 23:59:59";
        }
        
        System.out.println("\ninto GSStockQuantityMonthSumReportServlet");
        System.out.println("start"+start);
        System.out.println("end"+end);
    	
    	boolean isAvaliable = true;
    	boolean isLager = false;
    	
    	GSProviderDAO pd = new GSProviderDAO();
    	GSOrganizationDAO od = new GSOrganizationDAO();
    	
    	String append = "";//Long.toString(System.currentTimeMillis()).substring(3);

    	
    	try{
    	    String a1 = new String(FILENAME.getBytes("UTF-8"),"UTF-8");
    	    

    	    GSOperatorBean operatorBean = (GSOperatorBean)request.getSession().getAttribute(GSBaseConstants.LOGIN);
    	    String operOrgId = operatorBean.getGs_oper_org_id();
	    	if("11".equals(title) || "12".equals(title)){
	    	    String title1 = "采购明细表";
	    	    append = "";//"CGMX_"+operOrgId+"_"+append;
	    	    ArrayList ary = deptdetailquery(start,end,deptid);
	    	    if(ary == null || ary.size() == 0){
	    	        isAvaliable = false;
	    	    }else if("12".equals(title) && ary.size() >13000){
	    	    	isAvaliable = false;
	    	    	isLager = true;
	    	    	    
	    	    }else{
		    	    title1 = od.getOrg(deptid).getGs_org_name() + title1;
		    	    writeExcel_deptdetailquery(ary,title1,start,end,append);
	    	    }
	    	    
	    	}
	    	if("21".equals(title) || "22".equals(title)){
	    	    String title1 = "采购汇总表";
	    	    append = "";//"CGHZ_"+operOrgId+"_"+append;
	    	    ArrayList ary = deptsumquery(start,end,deptid);
	    	    if(ary == null || ary.size() == 0){
	    	        isAvaliable = false;
				}else if("22".equals(title) && ary.size() >13000){
					isAvaliable = false;
					isLager = true;	    	        
	    	    }else{
		    	    title1 = od.getOrg(deptid).getGs_org_name() + title1;
		    	    writeExcel_deptsumquery(ary,title1,start,end,append);
	    	    }
	    	    
	    	}
	    	if("31".equals(title) || "32".equals(title)){
	    	    String title1 = "供应明细表";
	    	    append = "";//"GYMX_"+operOrgId+"_"+append;
	    	    ArrayList ary = provdetailquery(start,end,deptid,operOrgId);
	    	    if(ary == null || ary.size() == 0){
	    	        isAvaliable = false;
				}else if("32".equals(title) && ary.size() >13000){
					isAvaliable = false;
					isLager = true;	    	        
	    	    }else{
		    	    title1 = pd.queryProviderByShortName(deptid).getGs_provider_name() + title1;
		    		writeExcel_provdetailquery(ary,title1,start,end,append);
	    	    }
	    	    
	    	}
	    	if("41".equals(title) || "42".equals(title)){
	    	    String title1 = "供应汇总表";
	    	    append = "";//"GYHZ_"+operOrgId+"_"+append;
	    	    ArrayList ary = provsumquery(start,end,deptid,operOrgId);
	    	    if(ary == null || ary.size() == 0){
	    	        isAvaliable = false;
				}else if("42".equals(title) && ary.size() >13000){
					isAvaliable = false;
					isLager = true;	    	        
	    	    }else{
		    	    title1 = pd.queryProviderByShortName(deptid).getGs_provider_name() + title1;
		    		writeExcel_provsumquery(ary,title1,start,end,append);
	    	    }
	    	    
	    	}
    	}catch(Exception e){
    	    
    	}

    	if(isAvaliable){
    	    try{
    	        response.reset();
    		    response.setContentType( "application/vnd.ms-excel;charset=UTF-16");
    	    	//response.setHeader("Content-disposition","attachment; filename="+GSBaseManager.toUtf8String(new String((FILENAME+ ".xls").getBytes("UTF-8"),"UTF-8")));
    		    //response.setHeader("Content-disposition","attachment; filename=" + (FILENAME+"_"+append + ".xls"));
    		    response.setHeader("Content-disposition","attachment; filename=" + (FILENAME + ".xls"));
	            
	    		String tOutputFile=GSSystemConfigurator.getParam("gs.backuppath")+"/" + (FILENAME) + ".xls";
	    		String tOutputPath=GSSystemConfigurator.getParam("gs.backuppath");

    			File tFile = new File(tOutputFile);
    	        FileInputStream tInputStream = new FileInputStream(tFile);
    	        OutputStream tOutputStream = response.getOutputStream();
    	        byte[] buff = new byte[1024];
    	        int readCount = 0;
    	        readCount = tInputStream.read(buff);
    	        while (readCount != -1){
    	            tOutputStream.write(buff, 0, readCount); 
    	           readCount = tInputStream.read(buff);
    	        }
    	        tOutputStream.flush();
    	        if (tInputStream!=null){
    	            tInputStream.close();
    	        }
    	        if (tOutputStream!=null){
    	            tOutputStream.close();
    	        }
    	    }catch(IOException ex){
    	    	ex.printStackTrace();
    	    }     
    	    
    	}else{
    		if (isLager)
    		{
				request.getSession().setAttribute("MSG","数据量过于巨大，请缩小查询值的范围");
    		}
    		else
    		{
    	    	request.getSession().setAttribute("MSG","没有查到数据,请重新输入查询条件");
    		}
    		
    	    try{
    	        request.getRequestDispatcher("/A300201.do").forward(request,response);
    	    }catch(Exception e){
    	        e.printStackTrace();
    	    }

    	}
    }
    public void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException {
    	doPost(request,response);
    }
    
    /**
     * 报表1
     * @param aList
     * @param title
     * @param start
     * @param end
     * @param append
     */
	private void writeExcel_deptdetailquery(ArrayList aList,String title,String start,String end,String append){
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	HSSFRow tRow = null;
	    	Region region = null;
	    	tSheet.setColumnWidth((short)0,(short)(16*256));
	    	tSheet.setColumnWidth((short)1,(short)(12*256));
	    	tSheet.setColumnWidth((short)2,(short)(12*256));
	    	tSheet.setColumnWidth((short)3,(short)(18*256));
	    	tSheet.setColumnWidth((short)4,(short)(10*256));
	    	tSheet.setColumnWidth((short)5,(short)(10*256));
	    	tSheet.setColumnWidth((short)6,(short)(10*256));
	    	tSheet.setColumnWidth((short)7,(short)(10*256));
	    	tSheet.setColumnWidth((short)8,(short)(12*256));
	    	tSheet.setColumnWidth((short)9,(short)(14*256));
	    	tSheet.setColumnWidth((short)10,(short)(14*256));
	    	tSheet.setColumnWidth((short)11,(short)(14*256));

	    	tWb.setSheetName(0,title,HSSFCell.ENCODING_UTF_16);

	    	HSSFFont font = tWb.createFont();
	        font.setFontHeightInPoints((short)9);
	        font.setFontName("宋体");
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	    	HSSFCellStyle cStyle = tWb.createCellStyle();
	        cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        HSSFCellStyle cStyleM = tWb.createCellStyle();
	        cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        cStyleM.setFont(font);
	        
	        HSSFCellStyle cStyleM_R = tWb.createCellStyle();
	        cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        cStyleM_R.setFont(font);
	        
//	        cStyleM_R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderRight(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderTop(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBottomBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setTopBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setRightBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setLeftBorderColor(HSSFColor.BLACK.index);

	    	//标题行
	    	tRow = tSheet.createRow((short)0);
	    	HSSFCell c0 = createCell(tWb,tRow,(short)5,title);
	    	c0.setCellStyle(cStyleM);
	    	//日期行
	    	tRow = tSheet.createRow((short)1);
	    	region = new Region(1, (short)10, 1, (short)11);
	    	tSheet.addMergedRegion(region);
	    	createCell(tWb,tRow,(short)10,start+" 至 "+end,HSSFCellStyle.ALIGN_CENTER,false);
	    	
	    	//表头行
	        tRow = tSheet.createRow((short)2);

	    	HSSFCell c1 = createCell(tWb,tRow,(short)0,"订货单位");
	    	HSSFCell c2 = createCell(tWb,tRow,(short)1,"产品编号");
	    	HSSFCell c3 = createCell(tWb,tRow,(short)2,"集中编码");
	    	HSSFCell c4 = createCell(tWb,tRow,(short)3,"产品名称");
	    	HSSFCell c5 = createCell(tWb,tRow,(short)4,"型号");
	    	HSSFCell c6 = createCell(tWb,tRow,(short)5,"规格");
	    	HSSFCell c7 = createCell(tWb,tRow,(short)6,"品牌");
	    	HSSFCell c8 = createCell(tWb,tRow,(short)7,"价格（元）");
	    	HSSFCell c9 = createCell(tWb,tRow,(short)8,"数量");
	    	HSSFCell c10 = createCell(tWb,tRow,(short)9,"采购时间");
	    	HSSFCell c11 = createCell(tWb,tRow,(short)10,"金额（元）");
	    	HSSFCell c12 = createCell(tWb,tRow,(short)11,"是否应急采购");
	    	c1.setCellStyle(cStyleM_R);c2.setCellStyle(cStyleM_R);
	    	c3.setCellStyle(cStyleM_R);c4.setCellStyle(cStyleM_R);
	    	c5.setCellStyle(cStyleM_R);c6.setCellStyle(cStyleM_R);
	    	c7.setCellStyle(cStyleM_R);c8.setCellStyle(cStyleM_R);
	    	c9.setCellStyle(cStyleM_R);c10.setCellStyle(cStyleM_R);
	    	c11.setCellStyle(cStyleM_R);c12.setCellStyle(cStyleM_R);
	    	
	    	String[] tTemp;
	    	BigDecimal lastValue = new BigDecimal("0");
 
	    	int regionBegin = 0;
	    	int regionEnd = 0;
	    	String currentDept = "";
	    	short index = 2 ; //当前所在行号	    	
	    	short index_b = 3;
	    	BigDecimal zongji = new BigDecimal("0");
			
			for(int i=0;i<aList.size();i++){
				String[] row = (String[])aList.get(i);	    		
    	        //創建一行
	    		tRow = tSheet.createRow((short)(index+1));
	    		//如果發現本單位所屬的運營公司和上一條記錄不同，説明上一條記錄所屬的運營公司已經統計完畢
	    		//要為他增加一個統計行
	    		if(!currentDept.equals(row[0])){
	    		    //如果本行為第一行，則不需要增加統計行
	    		    //如果本行不是第一行，則需要增加統計行
	    		    if(!"".equals(currentDept)){
	    		        region = new Region(index+1,(short)1,(short)(index+1),(short)11);
	    		        tSheet.addMergedRegion(region);
	    		        HSSFCell tongjiCell = tRow.createCell((short)1);
	    		        HSSFCell tongjiCell_END = tRow.createCell((short)11);
	    		        tongjiCell_END.setCellStyle(cStyleM_R);
	    		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    		        String viewValue = StringUtils.fmtDbl(lastValue.toString(),2);
	    		        tongjiCell.setCellValue("合计:"+viewValue+"元");
	    		        tongjiCell.setCellStyle(cStyleM_R);
	    		        
	    		        //把公司名称合并单元格
	    		        //region = new Region(index_b,(short)0,(short)(index+1),(short)0);
	    		        //tSheet.addMergedRegion(region);
	    		        //index_b = (short)(index + 2);

	    		        //先把订货单位的合计值计算到总计值里
	    		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    		        lastValue = new BigDecimal("0");
	    		        index++;
	    		        //創建的row被增加的小计行佔用了，新增加一行
	    		        tRow = tSheet.createRow((short)(index+1));
	    		    }
		    		currentDept = row[0];
		    		createCell(tWb,tRow,(short)0,row[0],HSSFCellStyle.ALIGN_CENTER_SELECTION ,false);
	    		}
//	    		createCell(tWb,tRow,(short)1,row[1],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)2,row[2],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)3,row[3],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)4,row[4],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)5,row[5],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)6,row[6],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)7,row[7],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)8,row[8],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)9,row[9],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)10,row[10],HSSFCellStyle.ALIGN_RIGHT,true);
	    		
				createCell(tWb,tRow,(short)0,row[0]);
				createCell(tWb,tRow,(short)1,row[1]);
				createCell(tWb,tRow,(short)2,row[2]);
				createCell(tWb,tRow,(short)3,row[3]);
				createCell(tWb,tRow,(short)4,row[4]);
				createCell(tWb,tRow,(short)5,row[5]);
				createCell(tWb,tRow,(short)6,row[6]);
				createCell(tWb,tRow,(short)7,row[7]);
				createCell(tWb,tRow,(short)8,row[8]);
				createCell(tWb,tRow,(short)9,row[9]);
				createCell(tWb,tRow,(short)10,row[10]);
	    		
	    		
	    		String orderid = row[11];
	    		if(orderid.indexOf("L")==-1){
	    		    createCell(tWb,tRow,(short)11,"正常采购");
	    		}else{
	    		    createCell(tWb,tRow,(short)11,"应急采购");
	    		}

	    		lastValue = lastValue.add(new BigDecimal(row[10]));
    	        index++;
    	        
    	        row = null;
	    	}
	    	if(index > 2){
		    	//創建最後一個統計行
		    	tRow = tSheet.createRow((short)(index+1));
		        HSSFCell cell_0 = tRow.createCell((short)0);
		        HSSFCell cell_11 = tRow.createCell((short)11);
		        cell_0.setCellStyle(cStyleM_R);
		        cell_11.setCellStyle(cStyleM_R);
		        HSSFCell tongjiCell = tRow.createCell((short)1);
		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		        String viewValue = StringUtils.format(lastValue.toString(),2);
		        tongjiCell.setCellValue("合计:"+viewValue+"元");
		        tongjiCell.setCellStyle(cStyleM_R);

		        region = new Region(index+1,(short)1,index+1,(short)11);
		        tSheet.addMergedRegion(region);
		        //region = new Region(index_b,(short)0,index+1,(short)0);
		        //tSheet.addMergedRegion(region);
		        
		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    	}
	    	//总计行
	    	if(index > 2){
		    	tRow = tSheet.createRow((short)(index+2));
		    	HSSFCell zongjiCell = tRow.createCell((short)0);
		        HSSFCell cell_1 = tRow.createCell((short)1);
		        HSSFCell cell_2 = tRow.createCell((short)2);
		        HSSFCell cell_3 = tRow.createCell((short)3);
		        HSSFCell cell_4 = tRow.createCell((short)4);
		        HSSFCell cell_5 = tRow.createCell((short)5);
		        HSSFCell cell_6 = tRow.createCell((short)6);
		        HSSFCell cell_7 = tRow.createCell((short)7);
		        HSSFCell cell_8 = tRow.createCell((short)8);
		        HSSFCell cell_9 = tRow.createCell((short)9);
		        HSSFCell cell_10 = tRow.createCell((short)10);
		    	HSSFCell zongjiCell_11 = tRow.createCell((short)11);
		    	zongjiCell.setCellStyle(cStyleM_R);
		        cell_1.setCellStyle(cStyleM_R);
		        cell_2.setCellStyle(cStyleM_R);
		        cell_3.setCellStyle(cStyleM_R);
		        cell_4.setCellStyle(cStyleM_R);
		        cell_5.setCellStyle(cStyleM_R);
		        cell_6.setCellStyle(cStyleM_R);
		        cell_7.setCellStyle(cStyleM_R);
		        cell_8.setCellStyle(cStyleM_R);
		        cell_9.setCellStyle(cStyleM_R);
		        cell_10.setCellStyle(cStyleM_R);
		    	zongjiCell_11.setCellStyle(cStyleM_R);
		    	zongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		    	String viewValue = StringUtils.format(zongji.toString(),2);
		    	zongjiCell.setCellValue("总计:"+viewValue+"元");
		    	region = new Region(index+2,(short)0,index+2,(short)11);
		    	tSheet.addMergedRegion(region);
	    	    
	    	}	    	
	    	
	    	File tFile = new File(GSSystemConfigurator.getParam("gs.backuppath")+"/"+(FILENAME) + ".xls");
	    	if(tFile.exists()) tFile.delete();
	    	tFile.createNewFile();
	    	FileOutputStream fileOut = new FileOutputStream(tFile);
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	/**
	 * 报表2
	 * @param aList
	 * @param title
	 * @param start
	 * @param end
	 */
	private void writeExcel_deptsumquery(ArrayList aList,String title,String start,String end,String append){
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	HSSFRow tRow = null;
	    	Region region = null;
	    	tSheet.setColumnWidth((short)0,(short)(16*256));
	    	tSheet.setColumnWidth((short)1,(short)(12*256));
	    	tSheet.setColumnWidth((short)2,(short)(12*256));
	    	tSheet.setColumnWidth((short)3,(short)(18*256));
	    	tSheet.setColumnWidth((short)4,(short)(10*256));
	    	tSheet.setColumnWidth((short)5,(short)(10*256));
	    	tSheet.setColumnWidth((short)6,(short)(10*256));
	    	tSheet.setColumnWidth((short)7,(short)(10*256));
	    	tSheet.setColumnWidth((short)8,(short)(12*256));

	    	tWb.setSheetName(0,title,HSSFCell.ENCODING_UTF_16);

	    	HSSFFont font = tWb.createFont();
	        font.setFontHeightInPoints((short)9);
	        font.setFontName("宋体");
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	    	HSSFCellStyle cStyle = tWb.createCellStyle();
	        cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        HSSFCellStyle cStyleM = tWb.createCellStyle();
	        cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        cStyleM.setFont(font);
	        
	        HSSFCellStyle cStyleM_R = tWb.createCellStyle();
	        cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        cStyleM_R.setFont(font);
	        //cStyleM_R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        //cStyleM_R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        //cStyleM_R.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        //cStyleM_R.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        //cStyleM_R.setBottomBorderColor(HSSFColor.BLACK.index);
	        //cStyleM_R.setTopBorderColor(HSSFColor.BLACK.index);
	        //cStyleM_R.setRightBorderColor(HSSFColor.BLACK.index);
	        //cStyleM_R.setLeftBorderColor(HSSFColor.BLACK.index);
	        
	    	//标题行
	    	tRow = tSheet.createRow((short)0);
	    	HSSFCell c0 = createCell(tWb,tRow,(short)4,title);
	    	c0.setCellStyle(cStyleM);
	    	//日期行
	    	tRow = tSheet.createRow((short)1);
	    	region = new Region(1, (short)7, 1, (short)8);
	    	tSheet.addMergedRegion(region);
	    	createCell(tWb,tRow,(short)7,start+" 至 "+end,HSSFCellStyle.ALIGN_CENTER,false);
	    	
	    	//表头行
	        tRow = tSheet.createRow((short)2);

	    	HSSFCell c1 = createCell(tWb,tRow,(short)0,"订货单位");
	    	HSSFCell c2 = createCell(tWb,tRow,(short)1,"产品编号");
	    	HSSFCell c3 = createCell(tWb,tRow,(short)2,"集中编码");
	    	HSSFCell c4 = createCell(tWb,tRow,(short)3,"产品名称");
	    	HSSFCell c5 = createCell(tWb,tRow,(short)4,"型号");
	    	HSSFCell c6 = createCell(tWb,tRow,(short)5,"规格");
	    	HSSFCell c7 = createCell(tWb,tRow,(short)6,"品牌");
	    	HSSFCell c8 = createCell(tWb,tRow,(short)7,"采购量");
	    	HSSFCell c9 = createCell(tWb,tRow,(short)8,"金额（元）");
	    	c1.setCellStyle(cStyleM_R);c2.setCellStyle(cStyleM_R);
	    	c3.setCellStyle(cStyleM_R);c4.setCellStyle(cStyleM_R);
	    	c5.setCellStyle(cStyleM_R);c6.setCellStyle(cStyleM_R);
	    	c7.setCellStyle(cStyleM_R);c8.setCellStyle(cStyleM_R);
	    	c9.setCellStyle(cStyleM_R);
	    	
	    	String[] tTemp;
	    	BigDecimal lastValue = new BigDecimal("0");
 
	    	int regionBegin = 0;
	    	int regionEnd = 0;
	    	String currentDept = "";
	    	int index = 2;//当前所在行号
	    	int index_b = 3;
	    	BigDecimal zongji = new BigDecimal("0");
	    	for(int i=0;i<aList.size();i++){
	    	    String[] row = (String[])aList.get(i);
    	        //創建一行
	    		tRow = tSheet.createRow((short)(index+1));
	    		//如果發現本單位所屬的運營公司和上一條記錄不同，説明上一條記錄所屬的運營公司已經統計完畢
	    		//要為他增加一個統計行
	    		if(!currentDept.equals(row[0])){
	    		    //如果本行為第一行，則不需要增加統計行
	    		    //如果本行不是第一行，則需要增加統計行
	    		    if(!"".equals(currentDept)){
	    		        region = new Region(index+1,(short)1,index+1,(short)8);
	    		        tSheet.addMergedRegion(region);
	    		        HSSFCell tongjiCell = tRow.createCell((short)1);
	    		        HSSFCell tongjiCell_END = tRow.createCell((short)8);
	    		        //tongjiCell_END.setCellStyle(cStyleM_R);
	    		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    		        String viewValue = StringUtils.format(lastValue.toString(),2);
	    		        tongjiCell.setCellValue("合计:"+viewValue+"元");
	    		        
	    		        tongjiCell.setCellStyle(cStyleM_R);

	    		        //把公司名称合并单元格
	    		        //region = new Region(index_b,(short)0,index+1,(short)0);
	    		        //tSheet.addMergedRegion(region);
	    		        //index_b = index+2;
	    		        
	    		        //先把订货单位的合计值计算到总计值里
	    		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    		        lastValue = new BigDecimal("0");
	    		        index++;
	    		        //創建的row被增加的小计行佔用了，新增加一行
	    		        tRow = tSheet.createRow((short)(index+1));
	    		    }
		    		currentDept = row[0];
	    		}
//	    		createCell(tWb,tRow,(short)0,row[0],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)1,row[1],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)2,row[2],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)3,row[3],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)4,row[4],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)5,row[5],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)6,row[6],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)7,row[7]+row[8],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)8,row[9],HSSFCellStyle.ALIGN_RIGHT,true);


				createCell(tWb,tRow,(short)0,row[0]);
				createCell(tWb,tRow,(short)1,row[1]);
				createCell(tWb,tRow,(short)2,row[2]);
				createCell(tWb,tRow,(short)3,row[3]);
				createCell(tWb,tRow,(short)4,row[4]);
				createCell(tWb,tRow,(short)5,row[5]);
				createCell(tWb,tRow,(short)6,row[6]);
				createCell(tWb,tRow,(short)7,row[7]+row[8]);
				createCell(tWb,tRow,(short)8,row[9]);
	    		

	    		lastValue = lastValue.add(new BigDecimal(row[9]));
    	        index++;
	    	}
	    	if(index > 2){
		    	//創建最後一個統計行
		    	tRow = tSheet.createRow((short)(index+1));

		    	HSSFCell tongjiCell = tRow.createCell((short)1);
		        HSSFCell cell_0 = tRow.createCell((short)0);
		        HSSFCell cell_8 = tRow.createCell((short)8);
		        //cell_0.setCellStyle(cStyleM_R);
		        //cell_8.setCellStyle(cStyleM_R);
		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		        String viewValue = StringUtils.format(lastValue.toString(),2);
		        tongjiCell.setCellValue("合计:"+viewValue+"元");
		       
		        tongjiCell.setCellStyle(cStyleM_R);
		        
		        region = new Region(index+1,(short)1,index+1,(short)8);
		        tSheet.addMergedRegion(region);
		        //region = new Region(index_b,(short)0,index+1,(short)0);
		        //tSheet.addMergedRegion(region);
		        
		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    	}
	    	//总计行
	    	if(index > 2){
		    	tRow = tSheet.createRow((short)(index+2));
		    	HSSFCell zongjiCell = tRow.createCell((short)0);

		        HSSFCell cell_1 = tRow.createCell((short)1);
		        HSSFCell cell_2 = tRow.createCell((short)2);
		        HSSFCell cell_3 = tRow.createCell((short)3);
		        HSSFCell cell_4 = tRow.createCell((short)4);
		        HSSFCell cell_5 = tRow.createCell((short)5);
		        HSSFCell cell_6 = tRow.createCell((short)6);
		        HSSFCell cell_7 = tRow.createCell((short)7);
		    	HSSFCell zongjiCell_11 = tRow.createCell((short)8);
		    	zongjiCell.setCellStyle(cStyleM_R);
//		        cell_1.setCellStyle(cStyleM_R);
//		        cell_2.setCellStyle(cStyleM_R);
//		        cell_3.setCellStyle(cStyleM_R);
//		        cell_4.setCellStyle(cStyleM_R);
//		        cell_5.setCellStyle(cStyleM_R);
//		        cell_6.setCellStyle(cStyleM_R);
//		        cell_7.setCellStyle(cStyleM_R);
//		    	zongjiCell_11.setCellStyle(cStyleM_R);
		    	zongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		    	String viewValue = StringUtils.format(zongji.toString(),2);
		    	zongjiCell.setCellValue("总计:"+viewValue+"元");
		    	region = new Region(index+2,(short)0,index+2,(short)8);
		    	tSheet.addMergedRegion(region);
	    	    
	    	}	    	
	    	
	    	
	    	File tFile = new File(GSSystemConfigurator.getParam("gs.backuppath")+"/"+(FILENAME)+".xls");
	    	if(tFile.exists()) tFile.delete();
	    	tFile.createNewFile();
	    	FileOutputStream fileOut = new FileOutputStream(tFile);
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	/**
	 * 报表3
	 * @param aList
	 * @param title
	 * @param start
	 * @param end
	 * @param append
	 */
	private void writeExcel_provdetailquery(ArrayList aList,String title,String start,String end,String append){
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	HSSFRow tRow = null;
	    	Region region = null;
	    	tSheet.setColumnWidth((short)0,(short)(16*256));
	    	tSheet.setColumnWidth((short)1,(short)(6*256));
	    	tSheet.setColumnWidth((short)2,(short)(12*256));
	    	tSheet.setColumnWidth((short)3,(short)(12*256));
	    	tSheet.setColumnWidth((short)4,(short)(18*256));
	    	tSheet.setColumnWidth((short)5,(short)(10*256));
	    	tSheet.setColumnWidth((short)6,(short)(10*256));
	    	tSheet.setColumnWidth((short)7,(short)(10*256));
	    	tSheet.setColumnWidth((short)8,(short)(10*256));
	    	tSheet.setColumnWidth((short)9,(short)(10*256));
	    	tSheet.setColumnWidth((short)10,(short)(14*256));
	    	tSheet.setColumnWidth((short)11,(short)(14*256));

	    	tWb.setSheetName(0,title,HSSFCell.ENCODING_UTF_16);

	    	HSSFFont font = tWb.createFont();
	        font.setFontHeightInPoints((short)9);
	        font.setFontName("宋体");
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	    	HSSFCellStyle cStyle = tWb.createCellStyle();
	        cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        HSSFCellStyle cStyleM = tWb.createCellStyle();
	        cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        cStyleM.setFont(font);
	        
	        HSSFCellStyle cStyleM_R = tWb.createCellStyle();
	        cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        cStyleM_R.setFont(font);
//	        cStyleM_R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderRight(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderTop(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBottomBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setTopBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setRightBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setLeftBorderColor(HSSFColor.BLACK.index);
	        
	    	//标题行
	    	tRow = tSheet.createRow((short)0);
	    	HSSFCell c0 = createCell(tWb,tRow,(short)5,title);
	    	c0.setCellStyle(cStyleM);
	    	//日期行
	    	tRow = tSheet.createRow((short)1);
	    	region = new Region(1, (short)9, 1, (short)10);
	    	tSheet.addMergedRegion(region);
	    	createCell(tWb,tRow,(short)9,start+" 至 "+end,HSSFCellStyle.ALIGN_CENTER,false);
	    	
	    	//表头行
	        tRow = tSheet.createRow((short)2);

	    	HSSFCell c1 = createCell(tWb,tRow,(short)0,"订货单位");
	    	HSSFCell c2 = createCell(tWb,tRow,(short)1,"序号");
	    	HSSFCell c3 = createCell(tWb,tRow,(short)2,"产品编号");
	    	HSSFCell c4 = createCell(tWb,tRow,(short)3,"集中编码");
	    	HSSFCell c5 = createCell(tWb,tRow,(short)4,"产品名称");
	    	HSSFCell c6 = createCell(tWb,tRow,(short)5,"型号");
	    	HSSFCell c7 = createCell(tWb,tRow,(short)6,"规格");
	    	HSSFCell c8 = createCell(tWb,tRow,(short)7,"品牌");
	    	HSSFCell c9 = createCell(tWb,tRow,(short)8,"价格（元）");
	    	HSSFCell c10 = createCell(tWb,tRow,(short)9,"数量");
	    	HSSFCell c11 = createCell(tWb,tRow,(short)10,"采购时间");
	    	HSSFCell c12 = createCell(tWb,tRow,(short)11,"金额（元）");
	    	c1.setCellStyle(cStyleM_R);c2.setCellStyle(cStyleM_R);
	    	c3.setCellStyle(cStyleM_R);c4.setCellStyle(cStyleM_R);
	    	c5.setCellStyle(cStyleM_R);c6.setCellStyle(cStyleM_R);
	    	c7.setCellStyle(cStyleM_R);c8.setCellStyle(cStyleM_R);
	    	c9.setCellStyle(cStyleM_R);c10.setCellStyle(cStyleM_R);
	    	c11.setCellStyle(cStyleM_R);c12.setCellStyle(cStyleM_R);
	    	
	    	String[] tTemp;
	    	BigDecimal lastValue = new BigDecimal("0");
 
	    	int regionBegin = 0;
	    	int regionEnd = 0;
	    	String currentDept = "";
	    	long xuhao = 1;//序号
	    	int index = 2;//当前所在行号	  
	    	int index_b = 3;
	    	BigDecimal zongji = new BigDecimal("0");//总计
	    	for(int i=0;i<aList.size();i++){
	    	    String[] row = (String[])aList.get(i);
    	        //創建一行
	    		tRow = tSheet.createRow((short)(index+1));
	    		//如果發現本單位所屬的運營公司和上一條記錄不同，説明上一條記錄所屬的運營公司已經統計完畢
	    		//要為他增加一個統計行
	    		if(!currentDept.equals(row[0])){
	    		    //如果本行為第一行，則不需要增加統計行
	    		    //如果本行不是第一行，則需要增加統計行
	    		    if(!"".equals(currentDept)){
	    		        region = new Region(index+1,(short)1,index+1,(short)11);
	    		        tSheet.addMergedRegion(region);
	    		        HSSFCell tongjiCell = tRow.createCell((short)1);
	    		        HSSFCell tongjiCell_END = tRow.createCell((short)11);
	    		        tongjiCell_END.setCellStyle(cStyleM_R);
	    		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    		        String viewValue = StringUtils.format(lastValue.toString(),2);
	    		        tongjiCell.setCellValue("合计:"+viewValue+"元");
	    		        tongjiCell.setCellStyle(cStyleM_R);
	    		        
	    		        //把公司名称合并单元格
	    		        //region = new Region(index_b,(short)0,index+1,(short)0);
	    		        //tSheet.addMergedRegion(region);
	    		        //index_b = index+2;

	    		        //先把订货单位的合计值计算到总计值里
	    		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    		        //清除上个订货单位的合计值
	    		        lastValue = new BigDecimal("0");
	    		        index++;
	    		        //創建的row被增加的小计行佔用了，新增加一行
	    		        tRow = tSheet.createRow((short)(index+1));
	    		    }
		    		currentDept = row[0];
		    		
	    		}
//	    		createCell(tWb,tRow,(short)0,row[0],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)1,Long.toString(xuhao),HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)2,row[1],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)3,row[2],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)4,row[3],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)5,row[4],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)6,row[5],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)7,row[6],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)8,row[7],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)9,row[8],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)10,row[9],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)11,row[10],HSSFCellStyle.ALIGN_RIGHT,true);


				createCell(tWb,tRow,(short)0,row[0]);
				createCell(tWb,tRow,(short)1,Long.toString(xuhao));
				createCell(tWb,tRow,(short)2,row[1]);
				createCell(tWb,tRow,(short)3,row[2]);
				createCell(tWb,tRow,(short)4,row[3]);
				createCell(tWb,tRow,(short)5,row[4]);
				createCell(tWb,tRow,(short)6,row[5]);
				createCell(tWb,tRow,(short)7,row[6]);
				createCell(tWb,tRow,(short)8,row[7]);
				createCell(tWb,tRow,(short)9,row[8]);
				createCell(tWb,tRow,(short)10,row[9]);
				createCell(tWb,tRow,(short)11,row[10]);	    		

	    		lastValue = lastValue.add(new BigDecimal(row[10]));
    	        index++;
    	        xuhao++;
	    	}
	    	if(index > 2){
		    	//創建最後一個統計行
		    	tRow = tSheet.createRow((short)(index+1));
		        HSSFCell tongjiCell = tRow.createCell((short)1);
		        HSSFCell tongjicell_11 = tRow.createCell((short)11);
		        tongjicell_11.setCellStyle(cStyleM_R);
		        
		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		        String viewValue =  StringUtils.format(lastValue.toString(),2);
		        tongjiCell.setCellValue("合计:"+viewValue+"元");
		        tongjiCell.setCellStyle(cStyleM_R);
		        
		        region = new Region(index+1,(short)1,index+1,(short)11);
		        tSheet.addMergedRegion(region);
		        //region = new Region(index_b,(short)0,index+1,(short)0);
		        //tSheet.addMergedRegion(region);
		        
		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    	}
	    	//总计行
	    	if(index > 2){
		    	tRow = tSheet.createRow((short)(index+2));
		    	HSSFCell zongjiCell = tRow.createCell((short)0);

		        HSSFCell cell_1 = tRow.createCell((short)1);
		        HSSFCell cell_2 = tRow.createCell((short)2);
		        HSSFCell cell_3 = tRow.createCell((short)3);
		        HSSFCell cell_4 = tRow.createCell((short)4);
		        HSSFCell cell_5 = tRow.createCell((short)5);
		        HSSFCell cell_6 = tRow.createCell((short)6);
		        HSSFCell cell_7 = tRow.createCell((short)7);
		        HSSFCell cell_8 = tRow.createCell((short)8);
		        HSSFCell cell_9 = tRow.createCell((short)9);
		        HSSFCell cell_10 = tRow.createCell((short)10);
		    	HSSFCell zongjiCell_11 = tRow.createCell((short)11);
		    	zongjiCell.setCellStyle(cStyleM_R);
		        cell_1.setCellStyle(cStyleM_R);
		        cell_2.setCellStyle(cStyleM_R);
		        cell_3.setCellStyle(cStyleM_R);
		        cell_4.setCellStyle(cStyleM_R);
		        cell_5.setCellStyle(cStyleM_R);
		        cell_6.setCellStyle(cStyleM_R);
		        cell_7.setCellStyle(cStyleM_R);
		        cell_8.setCellStyle(cStyleM_R);
		        cell_9.setCellStyle(cStyleM_R);
		        cell_10.setCellStyle(cStyleM_R);
		    	zongjiCell_11.setCellStyle(cStyleM_R);
		    	zongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		    	
		    	String viewValue = StringUtils.format(zongji.toString(),2);
		    	zongjiCell.setCellValue("总计:"+viewValue+"元");
		    	region = new Region(index+2,(short)0,index+2,(short)11);
		    	tSheet.addMergedRegion(region);
	    	    
	    	}
	    	
	    	File tFile = new File(GSSystemConfigurator.getParam("gs.backuppath")+"/"+(FILENAME)+".xls");
	    	if(tFile.exists()) tFile.delete();
	    	tFile.createNewFile();
	    	FileOutputStream fileOut = new FileOutputStream(tFile);
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	/**
	 * 报表4
	 * @param aList
	 * @param title
	 * @param start
	 * @param end
	 */
	private void writeExcel_provsumquery(ArrayList aList,String title,String start,String end,String append){
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	HSSFRow tRow = null;
	    	Region region = null;
	    	tSheet.setColumnWidth((short)0,(short)(16*256));
	    	tSheet.setColumnWidth((short)1,(short)(6*256));
	    	tSheet.setColumnWidth((short)2,(short)(12*256));
	    	tSheet.setColumnWidth((short)3,(short)(12*256));
	    	tSheet.setColumnWidth((short)4,(short)(18*256));
	    	tSheet.setColumnWidth((short)5,(short)(10*256));
	    	tSheet.setColumnWidth((short)6,(short)(10*256));
	    	tSheet.setColumnWidth((short)7,(short)(10*256));
	    	tSheet.setColumnWidth((short)8,(short)(10*256));
	    	tSheet.setColumnWidth((short)9,(short)(12*256));

	    	tWb.setSheetName(0,title,HSSFCell.ENCODING_UTF_16);

	    	HSSFFont font = tWb.createFont();
	        font.setFontHeightInPoints((short)9);
	        font.setFontName("宋体");
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	    	HSSFCellStyle cStyle = tWb.createCellStyle();
	        cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        HSSFCellStyle cStyleM = tWb.createCellStyle();
	        cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        cStyleM.setFont(font);
	        
	        HSSFCellStyle cStyleM_R = tWb.createCellStyle();
	        cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        cStyleM_R.setFont(font);
//	        cStyleM_R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderRight(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBorderTop(HSSFCellStyle.BORDER_THIN);
//	        cStyleM_R.setBottomBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setTopBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setRightBorderColor(HSSFColor.BLACK.index);
//	        cStyleM_R.setLeftBorderColor(HSSFColor.BLACK.index);
	        
	    	//标题行
	    	tRow = tSheet.createRow((short)0);
	    	HSSFCell c0 = createCell(tWb,tRow,(short)4,title);
	    	c0.setCellStyle(cStyleM);
	    	//日期行
	    	tRow = tSheet.createRow((short)1);
	    	region = new Region(1, (short)8, 1, (short)9);
	    	tSheet.addMergedRegion(region);
	    	createCell(tWb,tRow,(short)8,start+" 至 "+end,HSSFCellStyle.ALIGN_CENTER,false);
	    	
	    	//表头行
	        tRow = tSheet.createRow((short)2);

	    	HSSFCell c1 = createCell(tWb,tRow,(short)0,"订货单位");
	    	HSSFCell c2 = createCell(tWb,tRow,(short)1,"序号");
	    	HSSFCell c3 = createCell(tWb,tRow,(short)2,"产品编号");
	    	HSSFCell c4 = createCell(tWb,tRow,(short)3,"集中编码");
	    	HSSFCell c5 = createCell(tWb,tRow,(short)4,"产品名称");
	    	HSSFCell c6 = createCell(tWb,tRow,(short)5,"型号");
	    	HSSFCell c7 = createCell(tWb,tRow,(short)6,"规格");
	    	HSSFCell c8 = createCell(tWb,tRow,(short)7,"品牌");
	    	HSSFCell c9 = createCell(tWb,tRow,(short)8,"采购量");
	    	HSSFCell c10 = createCell(tWb,tRow,(short)9,"金额（元）");
	    	c1.setCellStyle(cStyleM_R);c2.setCellStyle(cStyleM_R);
	    	c3.setCellStyle(cStyleM_R);c4.setCellStyle(cStyleM_R);
	    	c5.setCellStyle(cStyleM_R);c6.setCellStyle(cStyleM_R);
	    	c7.setCellStyle(cStyleM_R);c8.setCellStyle(cStyleM_R);
	    	c9.setCellStyle(cStyleM_R);c10.setCellStyle(cStyleM_R);
	    	
	    	String[] tTemp;
	    	BigDecimal lastValue = new BigDecimal("0");
 
	    	int regionBegin = 0;
	    	int regionEnd = 0;
	    	long xuhao = 1;
	    	String currentDept = "";
	    	int index = 2;//当前所在行号	    
	    	int index_b = 3;//region起始行号
	    	BigDecimal zongji = new BigDecimal("0");//总计
	    	for(int i=0;i<aList.size();i++){
	    	    String[] row = (String[])aList.get(i);
    	        //創建一行
	    		tRow = tSheet.createRow((short)(index+1));
	    		//如果發現本單位所屬的運營公司和上一條記錄不同，説明上一條記錄所屬的運營公司已經統計完畢
	    		//要為他增加一個統計行
	    		if(!currentDept.equals(row[0])){
	    		    //如果本行為第一行，則不需要增加統計行
	    		    //如果本行不是第一行，則需要增加統計行
	    		    if(!"".equals(currentDept)){
	    		        region = new Region(index+1,(short)1,index+1,(short)9);
	    		        tSheet.addMergedRegion(region);
	    		        HSSFCell tongjiCell = tRow.createCell((short)1);
	    		        HSSFCell tongjiCell_END = tRow.createCell((short)9);
	    		        tongjiCell_END.setCellStyle(cStyleM_R);
	    		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    		        String viewValue  = StringUtils.format(lastValue.toString(),2);
	    		        tongjiCell.setCellValue("合计:"+viewValue+"元");
	    		        tongjiCell.setCellStyle(cStyleM_R);

	    		        //把公司名称合并单元格
	    		        //region = new Region(index_b,(short)0,index+1,(short)0);
	    		        //tSheet.addMergedRegion(region);
	    		        //index_b = index+2;
	    		        
	    		        //先把订货单位的合计值计算到总计值里
	    		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    		        //清除上个订货单位的合计值
	    		        lastValue = new BigDecimal("0");
	    		        index++;
	    		        //創建的row被增加的小计行佔用了，新增加一行
	    		        tRow = tSheet.createRow((short)(index+1));

	    		    }
		    		currentDept = row[0];
	    		}
//	    		createCell(tWb,tRow,(short)0,row[0],HSSFCellStyle.ALIGN_CENTER,true);
//	    		createCell(tWb,tRow,(short)1,Long.toString(xuhao),HSSFCellStyle.ALIGN_CENTER,true);
//	    		createCell(tWb,tRow,(short)2,row[1],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)3,row[2],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)4,row[3],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)5,row[4],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)6,row[5],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)7,row[6],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		createCell(tWb,tRow,(short)8,row[7]+row[8],HSSFCellStyle.ALIGN_RIGHT,true);
//	    		String row9 = new BigDecimal(row[7]).multiply(new BigDecimal(row[9])).toString();
//	    		createCell(tWb,tRow,(short)9,row9,HSSFCellStyle.ALIGN_RIGHT,true);

				createCell(tWb,tRow,(short)0,row[0]);
				createCell(tWb,tRow,(short)1,Long.toString(xuhao));
				createCell(tWb,tRow,(short)2,row[1]);
				createCell(tWb,tRow,(short)3,row[2]);
				createCell(tWb,tRow,(short)4,row[3]);
				createCell(tWb,tRow,(short)5,row[4]);
				createCell(tWb,tRow,(short)6,row[5]);
				createCell(tWb,tRow,(short)7,row[6]);
				createCell(tWb,tRow,(short)8,row[7]+row[8]);
				String row9 = new BigDecimal(row[7]).multiply(new BigDecimal(row[9])).toString();
				createCell(tWb,tRow,(short)9,row9);
	    		

	    		lastValue = lastValue.add(new BigDecimal(row9));
    	        index++;
    	        xuhao++;
	    	}
	    	if(index > 2){
		    	//創建最後一個統計行
		    	tRow = tSheet.createRow((short)(index+1));
		        HSSFCell tongjiCell = tRow.createCell((short)1);
		        HSSFCell cell_0 = tRow.createCell((short)0);
		        HSSFCell cell_9 = tRow.createCell((short)9);
		        cell_0.setCellStyle(cStyleM_R);
		        cell_9.setCellStyle(cStyleM_R);
		        
		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		        String viewValue = StringUtils.format(lastValue.toString(),2);
		        tongjiCell.setCellValue("合计:"+viewValue+"元");
		        tongjiCell.setCellStyle(cStyleM_R);
		        
		        region = new Region(index+1,(short)1,index+1,(short)9);
		        tSheet.addMergedRegion(region);
		        //region = new Region(index_b,(short)0,index+1,(short)0);
		        //tSheet.addMergedRegion(region);
		        
		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    	}
	    	//总计行
	    	if(index > 2){
		    	tRow = tSheet.createRow((short)(index+2));
		    	HSSFCell zongjiCell = tRow.createCell((short)0);

		        HSSFCell cell_1 = tRow.createCell((short)1);
		        HSSFCell cell_2 = tRow.createCell((short)2);
		        HSSFCell cell_3 = tRow.createCell((short)3);
		        HSSFCell cell_4 = tRow.createCell((short)4);
		        HSSFCell cell_5 = tRow.createCell((short)5);
		        HSSFCell cell_6 = tRow.createCell((short)6);
		        HSSFCell cell_7 = tRow.createCell((short)7);
		        HSSFCell cell_8 = tRow.createCell((short)8);
		    	HSSFCell zongjiCell_9 = tRow.createCell((short)9);
		    	zongjiCell.setCellStyle(cStyleM_R);
		        cell_1.setCellStyle(cStyleM_R);
		        cell_2.setCellStyle(cStyleM_R);
		        cell_3.setCellStyle(cStyleM_R);
		        cell_4.setCellStyle(cStyleM_R);
		        cell_5.setCellStyle(cStyleM_R);
		        cell_6.setCellStyle(cStyleM_R);
		        cell_7.setCellStyle(cStyleM_R);
		        cell_8.setCellStyle(cStyleM_R);
		    	zongjiCell_9.setCellStyle(cStyleM_R);
		    	zongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		    	String viewValue = StringUtils.format(zongji.toString(),2);
		    	zongjiCell.setCellValue("总计:"+viewValue+"元");		    	
		    	region = new Region(index+2,(short)0,index+2,(short)9);
		    	tSheet.addMergedRegion(region);
	    	    
	    	}
	    	
	    	File tFile = new File(GSSystemConfigurator.getParam("gs.backuppath")+"/"+(FILENAME)+".xls");
	    	if(tFile.exists()) tFile.delete();
	    	tFile.createNewFile();
	    	FileOutputStream fileOut = new FileOutputStream(tFile);
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue){
	    HSSFCell tCell = aRow.createCell(column);
	    tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    tCell.setCellValue(aValue);
	    return tCell;
	}
	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue, short aAlign,boolean border){
	    HSSFCell tCell = aRow.createCell(column);
	    tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    tCell.setCellValue(aValue);
	    HSSFCellStyle tCellStyle = aWb.createCellStyle();
	    tCellStyle.setAlignment(aAlign);
	    tCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    if(border){
	        tCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	    }
	    tCell.setCellStyle(tCellStyle);
	    return tCell;
	}
	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue, short aAlign, short bAlign){
	    HSSFCell tCell = aRow.createCell(column);
	    tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    tCell.setCellValue(aValue);
	    HSSFCellStyle tCellStyle = aWb.createCellStyle();
	    tCellStyle.setAlignment(aAlign);
	    tCellStyle.setVerticalAlignment(bAlign);
	    
	    tCell.setCellStyle(tCellStyle);
	    return tCell;
	}
	
	public static void main(String[] args){
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	tWb.setSheetName(0,"采购分析月报表",HSSFCell.ENCODING_UTF_16);
	    	HSSFRow tRow = tSheet.createRow((short)0);
	    	//起始行,起始列,结束行,结束列,起始和结束相同,表示只有1列
	    	Region region = new Region(5, (short)0, 4, (short)1);

	    	tSheet.addMergedRegion(region);
	    	HSSFRow row = tSheet.createRow((short)8);
	    	HSSFCell cell = row.createCell((short)3);
	    	cell.setCellValue("aaaa");
	    	
	    	File tFile = new File("c:/a.xls");
	    	FileOutputStream fileOut = new FileOutputStream(tFile);
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
	}
}
